About Connect ODBC for Btrieve
About Connect ODBC for Btrieve (Pervasive.SQL)

Connect ODBC for Btrieve (the "Btrieve driver") supports the following versions of Btrieve files in the Windows environments:

•	Btrieve version 6.x

•	Pervasive.SQL 7.x

•	Pervasive SQL 2000

The driver executes SQL statements directly on Btrieve databases.

The driver file name is IVBTRnn.DLL, where nn denotes the revision level.


Copyright

DataDirect Connect ODBC for Btrieve (Pervasive.SQL)

© 2000 MERANT. All rights reserved.

DataDirect, INTERSOLV, Middleware, and SequeLink, are registered trademarks, and Client/Server MiddleWare, DataDirect Connect ADO, DataDirect Connect Integrator, DataDirect Connect JDBC, DataDirect Connect ODBC, DataDirect Connect OLE DB, DataDirect Connect Premium, DataDirect Reflector, DataDirect SequeLink Integrator, MERANT, and WebDBLink are trademarks of MERANT. All other trademarks are the property of their respective owners.


Driver Requirements

To access a Btrieve database, you must be using the appropriate client software for the version of the Btrieve database to which you are connecting:

Database Versions	Client Names
Pervasive.SQL 2000	Pervasive.SQL 2000 client software
Pervasive.SQL 7.0	Pervasive.SQL 7.0 client software
Btrieve 6.15 for Windows 9x	Btrieve Developer’s Kit or  Btrieve WorkStation Client Engine
Btrieve 6.15 for Windows NT	Btrieve Developer’s Kit, Btrieve WorkStation Client Engine, or Btrieve Client/Server Database Engine

Before you attempt to access Btrieve files, you must incorporate existing Btrieve files into a Scalable SQL database. See Defining Table Structure for information on defining table structure.

NOTE: The Btrieve driver may experience problems if the Btrieve Microkernel Engine’s communication buffer size is smaller than the Btrieve driver’s Array Size attribute. You can increase the communication buffer size with the Pervasive Software Setup Utility. You can decrease the array size option when you configure a data source using the ODBC Btrieve Driver Setup dialog box, or when passing a connection string.


Managing Databases

If you already use Scalable SQL, the Btrieve driver can access your Scalable SQL databases directly. If not, your Btrieve files must be incorporated into a Scalable SQL database.

A Scalable SQL database is composed of data files that contain your records and data dictionary files that describe the database. The data files are Btrieve files. The data dictionary files are special Btrieve files that contain descriptions of the data files, views, fields, and indexes in your database.

All Btrieve files in a Scalable SQL database must reside in the same directory. In addition to the Btrieve data files, the three data dictionary files (FILE.DDF, FIELD.DDF, and INDEX.DDF) also must be in the directory.

Incorporating a Btrieve file into a Scalable SQL database does not change the Btrieve file in any way. You can continue to access the file directly with any existing Btrieve application.


Installing Btrieve to Process Transactions
Transactions

The Btrieve driver supports transactions. A transaction is a series of database changes that is treated as a single unit. In applications that don't use transactions, the Btrieve driver immediately executes Insert, Update, and Delete statements on the database files and the changes are automatically committed when the SQL statement is executed. You cannot undo these changes. In applications that use transactions, the Btrieve driver holds inserts, updates, and deletes until you issue a Commit or Rollback. A Commit saves the changes to the database file; a Rollback undoes the changes.

Transactions affect the removal of record locking. All locks are removed when SQLEndTran is called with the Commit or Rollback option to end the active transaction.

To use the Btrieve driver's transaction processing capabilities, consult the Pervasive documentation.


Configuring Data Sources

Data sources are configured and modified through the ODBC Administrator. To configure a Btrieve data source:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC Btrieve Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select DataDirect Btrieve and click Finish to display the ODBC Btrieve Driver Setup dialog box.

3	Specify a data source name, database directory and, optionally, a database description. Click Apply.

4	Click the Advanced tab to configure additional, optional settings for the data source. Click Apply.

5	Click Define to define table structure, as described in Defining Table Structure.

6	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. DataDirect provides a translator named "OEM to ANSI" that translates your data from the IBM PC character set to the ANSI character set.

Select a translator, then click OK to close this dialog box and perform the translation.

7	Click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

8	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Test Connection

At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

•	If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.

•	If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message.

Verify that the all required client software is properly installed. If it is not, you will see the message:

Specified driver could not be loaded due to system error [xxx].

Click OK.


Defining Table Structure

Because Btrieve does not store any column information in the data file, you may need to define its structure. Tables created by the DataDirect driver or by Scalable SQL will not require this. Utilities are also available from Pervasive that will perform this operation.

To define the structure of a file:

1	Display the ODBC Btrieve Driver Setup dialog box through the ODBC Administrator. Click the Advanced tab, then click Define to display the Define File dialog box.

2	Select the file you want to define and click Open to display the Define Table dialog box.

Database Name: The name of the Scalable SQL data dictionary directory that you selected in the Define File dialog box.

File: The name of the file that you selected in the Define File dialog box.

Table: Type the name of the table to be returned by SQLTables. The name can be up to 20 characters and cannot be the same as another defined table in the database. This field is required.

3	Enter values in the following fields to define each column. Click Add to add the column name to the list box.

Name: Type the name of the column.

Type: Select the data type of the column.

Length: Type the length of the column, if applicable.

Scale: Type the scale of the column, if applicable.

4	To modify an existing column definition, select the column name in the list box. Modify the values for that column name; then, click Modify.

5	To delete an existing column definition, select a column name in the list box and click Remove.

6	Click OK to define the table.


Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string  The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Btrieve is:

DSN=BTRIEVE FILES;DB=J:\Btrvdata

The paragraphs that follow list the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is the default.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications.

When set to 1 (the initial default), the driver is thread-safe.

When using the driver with single-threaded applications, you can set this option to 0 to avoid additional processing required for ODBC thread-safety standards.

ArraySize (AS): An integer value that enables the driver to retrieve an array of records from the Btrieve engine and in most cases results in better performance for the application. The value of ArraySize is the number of bytes in the array.

The initial default ArraySize is 4,096 bytes and the maximum is 65,535 bytes.

Database (DB): The full pathname of the directory that contains the Btrieve files and the data dictionary files (.DDF). Data dictionary files describe the structure of Btrieve data. If no directory is specified, the current working directory is used.

DataFileExtension (DFE): A string of three or fewer characters that specifies the file extension to use for data files. This value is used for all Create Table statements. If you execute a Create Table statement that uses an extension other than the one specified as the DataFileExtension value, an error occurs.

In other SQL statements, such as Select or Insert, you can specify an extension other than the DataFileExtension value. If you do not specify an extension value in these cases, the DataFileExtension value is used.

The initial default is DTA.

DataSourceName (DSN): A string that identifies a Btrieve data source configuration in the system information. Examples include "Accounting" or "Btrieve Files."

DeferQueryEvaluation (DQ): DeferQueryEvaluation={0 | 1}. Determines when a query is evaluated—after all records are read or each time a record is fetched.

When set to 0, the driver generates a result set when the first record is fetched. The driver reads all records, evaluates each one against the Where clause, and compiles a result set containing the records that satisfy the search criteria. This process slows performance when the first record is fetched, but activity performed on the result set after this point is much faster because the result set has already been created. You do not see any additions, deletions, or changes in the database that occur while working with this result set.

When set to 1 (the initial default), the driver evaluates the query each time another record is fetched and stops reading through the records when it finds one that matches the search criteria. This setting avoids the slowdown while fetching the first record, but each fetch takes longer because of the evaluation taking place. The data you retrieve reflect the latest changes to the database; however, a result set is still generated if the query is a Union of multiple Select statements, if it contains the Distinct keyword, or if it has an Order By or Group By clause.

FileOpenCache (FOC): An integer value that determines the maximum number of used file handles to cache. For example, when FileOpenCache=4, and a user opens and closes four tables, the tables are not actually closed. The driver keeps them open so that if another query uses one of these tables, the driver does not have to perform another open, which is expensive. The advantage of using file open caching is increased performance. The disadvantage is that a user who tries to open the file exclusively may get a file locking conflict even though no one appears to have the file open.

The initial default is 0, which means no file open caching.

IntlSort (IS): IntlSort={0 | 1}. Determines the order in which records are retrieved when you issue a Select statement with an Order By clause.

When set to 0 (the initial default), the driver uses the ASCII sort order. This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, "A, b, C" would be sorted as "A, C, b."

When set to 1, the driver uses the international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as "A, b, C." See your operating system documentation concerning the sorting of accented characters.

LogonID (UID): The default logon ID used to connect to your Btrieve database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID.

Password (PWD): The password that you must enter if your Scalable SQL data dictionary files have security restrictions set.

UndefinedTable (UT): UndefinedTable={PROMPT | ERROR}. Determines whether the driver should prompt the user when it encounters a table for which it has no structure information.

When set to PROMPT, the driver prompts the user.

When set to ERROR (the initial default), the driver returns an error.

UseLongQualifiers (ULQ): UseLongQualifiers={0 | 1}. Determines whether the driver uses long path names as table qualifiers.

When set to 0 (the initial default), the driver does not use long path names (the maximum path name length is 128 characters).

When set to 1, the driver uses long path names (the maximum path name length is 255 characters).


Data Types

The following table shows how the Btrieve data types map to the standard ODBC data types. The Btrieve data types are used when you incorporate Btrieve files into a Scalable SQL database.

Btrieve	ODBC
Autoincrement(2)	SQL_SMALLINT
Autoincrement(4)	SQL_INTEGER
Bfloat(4)	SQL_REAL
Bfloat(8)	SQL_DOUBLE
Bit	SQL_BIT
Blob	SQL_LONGVARGBINARY
Char	SQL_CHAR
Currency	SQL_DECIMAL
Date	SQL_TYPE_DATE
Decimal	SQL_DECIMAL
Float(4)	SQL_REAL
Float(8)	SQL_DOUBLE
Integer(1)	SQL_TINYINT
Integer(2)	SQL_SMALLINT
Integer(4)	SQL_INTEGER
Integer(8)	SQL_BIGINT
Logical(1)	SQL_BIT
Logical(2)	SQL_BIT
Lstring	SQL_VARCHAR
Money	SQL_DECIMAL
Note	SQL_LONGVARCHAR
Numeric	SQL_NUMERIC
Numericsts	SQL_NUMERIC
Time	SQL_TYPE_TIME
Timestamp	SQL_TYPE_TIMESTAMP
Unsigned(1)	SQL_TINYINT
Unsigned(8)	SQL_BIGINT
Zstring	SQL_VARCHAR


Indexes

NOTE: If you define an index using the Btrieve driver, the index will not have the restrictions discussed here.

For query optimization, the Btrieve driver does not use the following:

•	Indexes containing all-segment-null keys or any-segment-null keys.

•	Any index key that is marked case-insensitive.

•	Any index keys where the data type of the index key does not match the data type of the field. The one exception is if the index key is declared as an unsigned integer and the field in the file is declared as signed integer, or vice versa, then the driver assumes the field contains only unsigned quantities and uses the index. Note that this can lead to incorrect results if the field in fact does contain signed quantities.

The Btrieve driver only uses an alternate-collating-sequence (ASC) index key for equality lookups. Additionally, if an ASC key is part of a segmented index, the other index segments are not used for query optimization unless the Where clause contains an equality condition for the ASC key.


Column Names

Column names in SQL statements (such as Select and Insert) can be up to 20 characters long. If column names are in all lowercase, a combination of upper and lowercase, contain blank spaces, or are reserved words, they must be surrounded by the grave character ( ` ) (ASCII 96). For example:

SELECT `name` FROM emp


Select Statement

You use the SQL Select statement to specify the columns and records to be read. Btrieve Select statements support all the Select statement clauses, as described in the Connect ODBC Help online help file. The following topics describe SQL statement information that is specific to the Btrieve driver.

Rowid Psuedo-Column

Alter Table Statement

Create and Drop Index Statements for Btrieve


ROWID Pseudo-Column

Each Btrieve record contains a special column named Rowid. This field contains a unique number that indicates the record's sequence in the database. You can use the Rowid in Where and Select clauses.

Rowid is particularly useful when you are updating records. You can retrieve the Rowid of the records in the database along with the other field values. For example:

SELECT last_name, first_name, salary, rowid FROM emp

Then you can use the Rowid of the record that you want to update to ensure that you are updating the correct record and no other. For example:

UPDATE emp set salary = 40000 FROM emp WHERE rowid=21

The fastest way of updating a single row is to use a Where clause with the Rowid. You cannot update the Rowid column.

Select statements that use the Rowid pseudo-column in the Where clause achieve maximum performance only for exact equality matches. If you use range scans instead of exact equality matches, a full table scan is performed. For example:

SELECT * FROM emp WHERE rowid=21    //fast search

SELECT * FROM emp WHERE rowid <=25  //full table scan


Alter Table Statement

The Btrieve driver supports the Alter Table statement to add one or more columns to a table or to delete (drop) a single column.

The Alter Table statement has the form:

ALTER TABLE table_name {ADD column_name data_type | ADD (column_name data_type [, column_name data_type] . . . ) |
DROP [COLUMN] column_name}

table_name is the name of the table to which you are adding or dropping columns.

column_name assigns a name to the column you are adding or specifies the column you are dropping. See Column Names for more information.

data_type specifies the native data type of each column you add. See Data Types for more information.

For example, to add two columns to the emp table:

ALTER TABLE emp {ADD startdate date, dept char 10)

You cannot add and drop columns in a single statement, and you can drop only one column at a time. For example, to drop a column:

ALTER TABLE emp DROP startdate

The Alter Table statement fails when you attempt to drop a column upon which other objects, such as indexes or views, are dependent.


Create and Drop Index Statements

The Btrieve driver supports SQL statements to create and delete indexes. The Create Index statement is used to create indexes and the Drop Index is used to delete indexes.

Create Index

The Create Index statement for Btrieve files has the form:

CREATE [UNIQUE] INDEX index_name ON table_name
([field_name [ASC | DESC] [,field_name [ASC | DESC]]...)

UNIQUE means that Btrieve does not let you insert two records with the same index values.

index_name is the name of the index.

table_name is the name of the table on which the index is to be created.

ASC tells Btrieve to create the index in ascending order. DESC tells Btrieve to create the index in descending order. By default, indexes are created in ascending order.

For example:

CREATE INDEX lname ON emp (last_name)

Drop Index

The form of the Drop Index statement is:

DROP INDEX table_name.index_name

table_name is the name of the table from which the index is to be dropped.

index_name is the name of the index.

For example:

DROP INDEX emp.lname


Isolation and Lock Levels Supported

Btrieve supports isolation level 1 (read committed) only. Btrieve supports record-level locking.


ODBC Conformance Level

The API functions supported are listed in “Supported ODBC Functions,” found in the General Help section of the Connect ODBC Help file. In addition, the following function is supported: SQLSetPos.

The Btrieve driver also supports backward and random fetching in SQLExtendedFetch and SQLFetchScroll. The driver supports the minimum SQL grammar with several core extensions.


Number of Connections and Statements Supported

Btrieve files support a single connection and multiple statements per connection.


General Tab, ODBC Btrieve Driver Setup

Use the ODBC Btrieve Driver Setup dialog box to create new Btrieve data sources or configure existing data sources.

Data Source Name: Type a string that identifies this Btrieve data source configuration in the system information. Examples include "Accounting" or "Btrieve Files."

Description: Type an optional long description of a data source name. For example, "My Accounting Database" or "Btrieve files in C:\ACCOUNTS."

Database Directory: Type the full pathname of the directory that contains the Btrieve files and the data dictionary files (.DDF). Data dictionary files describe the structure of Btrieve data. If no directory is specified, the current working directory is used.

Advanced tab

Displays the Advanced tab, where you can configure additional options for this data source.

OK

Cancel

Apply

Test Connect


Advanced Tab, ODBC Btrieve Driver Setup

Use the Advanced tab on the ODBC Btrieve Driver Setup dialog box to specify optional settings when you create new Btrieve data sources or configure existing data sources.

Default Logon ID: Type the default logon ID used to connect to your Btrieve database. A logon ID is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in a connection string.

File Open Cache: Type the numeric value to specify the maximum number of used file handles to cache. For example, the value 4 specifies that when a user opens and closes four tables, the tables are not actually closed. The driver keeps them open so that if another query uses one of these tables, the driver does not have to perform another open, which is expensive. The advantage of file open caching is increased performance. The disadvantage is that a user who specifies file locking on open may get a locking conflict even though no one appears to have the file open. The default is 0, which means no file open caching.

Array Size: Type the number of bytes in the array. This attribute enables the driver to retrieve an array of records from the Btrieve engine and in most cases results in better performance for the application. The default value is 4,096 bytes, and the maximum is 65,535 bytes.

Data File Extension: Type a string of three or fewer characters that specifies the file extension to use for data files. The default value is DTA. This value is used for all Create Table statements. Sending a Create Table statement that uses an extension other than the one specified as the DataFileExtension value causes an error.

In other SQL statements, such as Select or Insert, you can specify an extension other than the DataFileExtension value. If you do not specify an extension value in these cases, the DataFileExtension value is used.

Action for Undefined Tables: Select one of these options to indicate whether the driver should prompt the user when it encounters a table for which it has no structure information. Select the Prompt for Definition option to prompt the user; select the Return Error Condition option (the default) to return an error.

International Sort: Select this check box to indicate the order in which records are retrieved when you issue a Select statement with an Order By clause. Clear this box to use ASCII sort order (the default setting). This order sorts items alphabetically with uppercase letters preceding lowercase letters. For example, "A, b, C" would be sorted as "A, C, b."

Select this check box to use international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as "A, b, C." See your operating system documentation concerning the sorting of accented characters.

Use Long Qualifiers: Select this check box to specify whether the driver uses long path names. If you select the Use Long Qualifiers check box, path names can be up to 255 characters. If the check box is cleared (the default), the maximum path name length is 128 characters.

Application Using Threads: Select this check box to ensure that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread-safety standards.

Define Button

Displays the Define File dialog box, where you can define the structure of your files.

Translate Button

Displays the Select Translator dialog box, where you can translate your data from one character set to another. Choose the "OEM to ANSI" translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply

Test Connect


Define Table Dialog
Define Table Dialog Box

Use the Define Table dialog box to define the structure of the data file.

Table Information Pane

Database Name: The name of the Scalable SQL data dictionary directory that you selected in the Define File dialog box.

File: The name of the file that you selected in the Define File dialog box.

Table: Type the name of the table to be returned by SQLTables. The name can be up to 20 characters and cannot be the same as another defined table in the database. This field is required.

Column Information Pane

Name: Type the name of the column.

Type: Select the data type of the column.

Length: Type the length of the column, if applicable.

Scale: Type the scale of the column, if applicable.

Add Button

Adds a column definition and displays the name in the Columns list box.

Modify Button

Modifies the definition of the column selected in the Columns list box with the values you specify.

Remove Button

Removes the column selected in the Columns list box.

OK

Saves the table definition and closes the Define Table dialog box.

Cancel

Closes the Define Table dialog box without defining a table.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.


Contacting Technical Support

MERANT provides technical support for all registered users of this product, including limited installation support, for the first 30 days. For support after that time, contact us using one of the following methods or purchase further support by enrolling in the SupportNet program. For more information about SupportNet, contact your sales representative.

The MERANT Web site provides the latest support information through SupportNet Online, our global service network that provides access to valuable tools and information. Our SupportNet users access information using the Web, automatic email notification, newsgroups, and regional user groups. SupportNet Online includes a knowledge base that allows you to search on keywords for technical bulletins and other information. You also can download product fixes for your DataDirect products.

World Wide Web

http://support.merant.com

E-Mail

USA, Canada, and Mexico	datadirect.answerline@merant.com
Australia and New Zealand	australia.answerline@merant.com
Japan	jpn.answerline@merant.co.jp
All other countries	int.datadirect.answerline@merant.com
Local Telephone Support

Local phone numbers can be found at http://support.merant.com/websupport/contact/supportnetanswerline.asp.

Live Answerline telephone support is available 24 hours a day, seven days a week.

Fax and Mail Information

Fax US	1 919 461 4527
Fax Int.	+32 (0) 15 32 09 19
Mail	1500 Perimeter Park Drive, Suite 100, Morrisville, NC 27560 USA

Information to Provide

When you contact us, please include this information:

•	The product serial number located on the Product Registration Information card or on a product serial number card in your package. The number will be checked to verify your support eligibility. If you do not have a SupportNet contract, we will ask you to speak with a sales representative.

•	Your name and organization. For a first-time call, you may be asked for full customer information, including location and contact details.

•	The version number of your DataDirect product.

•	The type and version of your operating system.

•	Any third-party software or other environment information required to understand the problem.

•	A brief description of the problem, including any error messages that you have received, and the steps preceding the occurrence of the problem. Depending on the complexity of the problem, you may be asked to submit an example so that we can recreate the problem.

•	An assessment of the severity level of the problem.